﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using GovernmentOfficeProject.LevelDataAccessModel;
using GovernmentOfficeProject.SysTools;
using System.Data.SqlClient;
using System.Data;
using System.Text;

namespace GovernmentOfficeProject.LevelDataAccess
{
    public class DAL_USER_Relation
    {
        public Entity_USER_Relation getRelationByRelationID(decimal relationID)
        {
            try
            {
                Entity_USER_Relation relation = null;
                string strSQL = "select * from [PER_USER_RELATION] where URID=@relationID";
                SqlParameter[] values = new SqlParameter[]{
                    new SqlParameter("@relationID",relationID)
                };
                DataSet dst = DAL_DBGM.GetDataSet(strSQL, values);
                foreach (DataRow row in dst.Tables[0].Rows)
                {
                    relation = new Entity_USER_Relation();
                    relation.UrID = Convert.ToDecimal(row["URID"]);
                    relation.UrMasterUserID = Convert.ToDecimal(row["URMasterUserID"]);
                    relation.UrControlledUserID = Convert.ToDecimal(row["URControlledUserID"]);
                    relation.UrControlledPrivilegeID = Convert.ToDecimal(row["URControlledPrivilegeID"]);
                    relation.UrStatus = Convert.ToDecimal(row["URStatus"]);
                    relation.UrBackup = Convert.ToString(row["URBackup"]);
                }
                return relation;
            }
            catch (System.Exception ex)
            {
                AppLog.Write("---DAL_USER_Relation.getRelationByRelationID(decimal relationID)---", AppLog.LogMessageType.Error);
                throw ex;
            }
        }
        
        public List<Entity_USER_Relation> getRelationListByRelationEntity(Entity_USER_Relation relationEntity)
        {
            try
            {
                string strSQL = "select * from [PER_USER_RELATION] where 1=1";
                if (relationEntity.UrMasterUserID != -1)
                {
                    strSQL += " and URMasterUserID=" + relationEntity.UrMasterUserID;
                }
                if (relationEntity.UrControlledUserID != -1)
                {
                    strSQL += " and URControlledUserID=" + relationEntity.UrControlledUserID;
                }
                if (relationEntity.UrControlledPrivilegeID != -1)
                {
                    strSQL += " and URControlledPrivilegeID=" + relationEntity.UrControlledPrivilegeID;
                }
                if (relationEntity.UrStatus != -1)
                {
                    strSQL += " and URStatus=" + relationEntity.UrStatus;
                }
                if (relationEntity.UrBackup != "" && relationEntity.UrBackup != null)
                {
                    strSQL += " and URBackup='" + relationEntity.UrBackup + "'";
                }
                List<Entity_USER_Relation> relationList = new List<Entity_USER_Relation>();                
                DataSet dst = DAL_DBGM.GetDataSet(strSQL);
                foreach (DataRow row in dst.Tables[0].Rows)
                {
                    Entity_USER_Relation relation = new Entity_USER_Relation();
                    relation.UrID = Convert.ToDecimal(row["URID"]);
                    relation.UrMasterUserID = Convert.ToDecimal(row["URMasterUserID"]);
                    relation.UrControlledUserID = Convert.ToDecimal(row["URControlledUserID"]);
                    relation.UrControlledPrivilegeID = Convert.ToDecimal(row["URControlledPrivilegeID"]);
                    relation.UrStatus = Convert.ToDecimal(row["URStatus"]);
                    relation.UrBackup = Convert.ToString(row["URBackup"]);
                    relationList.Add(relation);
                }
                return relationList;
            }
            catch (System.Exception ex)
            {
                AppLog.Write("---DAL_USER_Relation.getRelationListByRelationEntity(Entity_USER_Relation relationEntity)---", AppLog.LogMessageType.Error);
                throw ex;
            }
        }

        public bool addRelation(Entity_USER_Relation relationEntity)
        {
            try
            {
                bool flag = false;
                bool blState = false;
                StringBuilder sqlBudIns = new StringBuilder();
                StringBuilder sqlBudValue = new StringBuilder();
                sqlBudIns.Append("insert into [PER_USER_RELATION](");
                sqlBudValue.Append(" values(");
                List<SqlParameter> sprList = new List<SqlParameter>();
                
                if (relationEntity.UrMasterUserID != -1)
                {
                    sqlBudIns.Append("URMasterUserID,");
                    sqlBudValue.Append("@masterUserID,");
                    sprList.Add(new SqlParameter("@masterUserID", relationEntity.UrMasterUserID));
                    blState = true;
                }
                if (relationEntity.UrControlledUserID != -1)
                {
                    sqlBudIns.Append("URControlledUserID,");
                    sqlBudValue.Append("@controlledUserID,");
                    sprList.Add(new SqlParameter("@controlledUserID", relationEntity.UrControlledUserID));
                    blState = true;
                }
                if (relationEntity.UrControlledPrivilegeID != -1)
                {
                    sqlBudIns.Append("UrControlledPrivilegeID,");
                    sqlBudValue.Append("@controlledPrivilegeID,");
                    sprList.Add(new SqlParameter("@controlledPrivilegeID", relationEntity.UrControlledPrivilegeID));
                    blState = true;
                }
                if (relationEntity.UrStatus != -1)
                {
                    sqlBudIns.Append("URStatus,");
                    sqlBudValue.Append("@status,");
                    sprList.Add(new SqlParameter("@status", relationEntity.UrStatus));
                    blState = true;
                }
                if (relationEntity.UrBackup!="" && relationEntity.UrBackup!=null)
                {
                    sqlBudIns.Append("URBackup");
                    sqlBudValue.Append("@backup,");
                    sprList.Add(new SqlParameter("@backup", relationEntity.UrBackup));
                    blState = true;
                }
                if (blState)
                {
                    string strSQL = sqlBudIns.ToString().Substring(0, sqlBudIns.Length - 1) + ")" + sqlBudValue.ToString().Substring(0, sqlBudValue.Length - 1) + ")";
                    SqlParameter[] values = sprList.ToArray();
                    //AppLog.Write("DAL-增加Relation的SQL语句: " + strSQL, AppLog.LogMessageType.Debug);
                    flag = DAL_DBGM.InsertInfo(strSQL, values);
                }
                else
                {
                    //添加用户失败:没有任何数据.
                    AppLog.Write("添加Relation失败:没有任何数据.", AppLog.LogMessageType.Info);
                }
                return flag;
            }
            catch (System.Exception ex)
            {
                AppLog.Write("---DAL_USER_Relation.addRelation(Entity_USER_Relation relationEntity)---", AppLog.LogMessageType.Error);
                throw ex;
            }
        }

        public bool updateRelationByRelationIDAndRelationEntity(decimal relID, Entity_USER_Relation relEntity)
        {
            try
            {
                bool flag = false;
                bool blState = false;
                string strSQL = "update [PER_USER_RELATION] set";

                if (relEntity.UrMasterUserID != -1)
                {
                    strSQL += " URMasterUserID="+relEntity.UrMasterUserID+",";
                    blState = true;
                }
                if (relEntity.UrControlledUserID != -1)
                {
                    strSQL += " URMControllerUserID=" + relEntity.UrControlledUserID + ",";
                    blState = true;
                }
                if (relEntity.UrControlledPrivilegeID != -1)
                {
                    strSQL += " URMControlledPrivilegeID=" + relEntity.UrControlledPrivilegeID + ",";
                    blState = true;
                }
                if (relEntity.UrStatus != -1)
                {
                    strSQL += " URStatus=" + relEntity.UrStatus + ",";
                    blState = true;
                }
                if (relEntity.UrBackup!="" && relEntity.UrBackup!=null)
                {
                    strSQL += " URBackup=" + relEntity.UrBackup + ",";
                    blState = true;
                }

                if (blState)
                {
                    strSQL = strSQL.Substring(0, strSQL.Length - 1) + " where URID = @relationID";
                    SqlParameter[] values = new SqlParameter[]{
                        new SqlParameter("@relationID",relID)
                    };
                    AppLog.Write(strSQL, AppLog.LogMessageType.Debug);
                    flag = DAL_DBGM.UpdateInfo(strSQL, values);
                    return flag;
                }
                else
                {
                    //没有需要更新的内容.
                    return flag;
                }                
            }
            catch (System.Exception ex)
            {
                AppLog.Write("---DAL_USER_Relation. updateRelationByRelationIDAndRelationEntity(decimal relID, Entity_USER_Relation relEntity)---", AppLog.LogMessageType.Error);
                throw ex;
            }

        }

        public bool deleteRelationByRelationID(decimal relationID)
        {
            try
            {
                string strSQL = "delete from [PER_USER_RELATION] where URID=@relationID";
                SqlParameter[] values = new SqlParameter[]{
                    new SqlParameter("@relationID",relationID)
                };
                return DAL_DBGM.DeleteInfo(strSQL,values);
            }
            catch (System.Exception ex)
            {
                AppLog.Write("---DAL_USER_Relation.getRelationByRelationID(decimal relationID)---", AppLog.LogMessageType.Error);
                throw ex;
            }
        }

        public List<Entity_USER_Relation> getRelationList()
        {
            try
            {
                string strSQL = "select * from [PER_USER_RELATION]";             
                List<Entity_USER_Relation> relationList = new List<Entity_USER_Relation>();
                DataSet dst = DAL_DBGM.GetDataSet(strSQL);
                foreach (DataRow row in dst.Tables[0].Rows)
                {
                    Entity_USER_Relation relation = new Entity_USER_Relation();
                    relation.UrID = Convert.ToDecimal(row["URID"]);
                    relation.UrMasterUserID = Convert.ToDecimal(row["URMasterUserID"]);
                    relation.UrControlledUserID = Convert.ToDecimal(row["URControlledUserID"]);
                    relation.UrControlledPrivilegeID = Convert.ToDecimal(row["URControlledPrivilegeID"]);
                    relation.UrStatus = Convert.ToDecimal(row["URStatus"]);
                    relation.UrBackup = Convert.ToString(row["URBackup"]);
                    relationList.Add(relation);
                }
                return relationList;
            }
            catch (System.Exception ex)
            {
                AppLog.Write("---DAL_USER_Relation.getRelationList()---", AppLog.LogMessageType.Error);
                throw ex;
            }
        }
    }

}